package com.internship.javawebinternship.repository;

import ch.qos.logback.core.read.ListAppender;
import com.internship.javawebinternship.entity.Account;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.security.cert.X509Certificate;
import java.util.List;


public interface AccountRepository extends JpaRepository<Account, String> {

    public List<Account> findByEmailAndPassword(String email, String password);

    public List<Account> findByEmailOrStuempnoAndPassword(String email, String no, String password);

    public List<Account> findByEmailAndStuempno(String email, String no);

    public List<Account> findByStuempno(String no);

//    public List<Account> findByAuthorityAndClassesAndGradeAndProfession(String authority, String classes, String grade, String profession);

    //字符串类型在 mysql 为 null 时,为 '' 字符串,如果为空,就使用 1 = 1 条件去查询
//    @Transactional(rollbackFor = Exception.class)
//    @Modifying
//    @Query(value = "select * from covid19.account where if(?1 !='',account.authority = ?1,1=1) and if(?2 !='',account.grade=?2,1=1)" +
//            " and if(?3!='',classes=?3,1=1) and if(?4 !='',profession=?4,1=1) ", nativeQuery = true)
//    public List<Account> findUserGroup(String authority, String grade, String classes, String profession);

    public List<Account> findByAuthority(String authority);

    @Transactional(rollbackFor = Exception.class)
    @Modifying
    public void deleteByRegistertime(String time);

    public int countByAuthority(String authority);

    public List<Account> findByAuthorityOrderByAuthorityAsc(String authority);

    public int countByEmail(String email);

    public List<Account> findByEmail(String email);

    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query(value = "SELECT * FROM covid19.account ORDER BY covid19.account.authority ASC ", nativeQuery = true)
    public List<Account> findAllUser();

    @Transactional(rollbackFor = Exception.class)
    @Modifying
    public void deleteByStuempno(String no);

}